package model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.mybeans.dao.DAOException;
import org.mybeans.factory.BeanFactory;
import org.mybeans.factory.BeanFactoryException;
import org.mybeans.factory.BeanTable;
import org.mybeans.factory.DuplicateKeyException;
import org.mybeans.factory.MatchArg;
import org.mybeans.factory.RollbackException;
import org.mybeans.factory.Transaction;

import databeans.Question;
import interfas.QuestionInterface;

public class QuestionDAO implements QuestionInterface {
	private List<Connection> connectionPool = new ArrayList<Connection>();
	private String jdbcDriver;
	private String jdbcURL;
	private BeanFactory<Question> factory;
	private int qid = 1;
	
	public QuestionDAO(String jdbcDriver, String jdbcURL) throws DAOException {
		try {
			this.jdbcDriver = jdbcDriver;
			this.jdbcURL = jdbcURL;
			BeanTable<Question> table = BeanTable.getInstance(Question.class,"question");
			if (!table.exists())
				table.create("qid");
			
			table.setIdleConnectionCleanup(true);
			factory = table.getFactory();
			
			if (factory.getBeanCount() > 0)
				qid = factory.match(MatchArg.max("qid"))[0].getQid() + 1;
			
		} catch (BeanFactoryException e) {
			throw new DAOException(e);
		} catch (RollbackException e) {
			throw new DAOException(e);
		}
	}
	
	private synchronized Connection getConnection() throws DAOException {
		if (connectionPool.size() > 0) {
			return connectionPool.remove(connectionPool.size() - 1);
		}
		try {
			Class.forName(jdbcDriver);
		} catch (ClassNotFoundException e){
			throw new DAOException(e);
		}
		try {
			return DriverManager.getConnection(jdbcURL);
		} catch (SQLException e) {
			throw new DAOException(e);
		}
	}
	
	private synchronized void releaseConnection(Connection con) {
		connectionPool.add(con);
	}
	
	public void create(Question question, SurveyDAO surveyDAO, EntityClassDAO ecDAO) throws DAOException {
		try {
			// Check for foreign key constraint
			if (surveyDAO.lookup(question.getSid()) == null) {
				throw new DAOException("sid must exist in survey table");
			}
			if (!(question.getEntityFrom().equals("")) && ecDAO.lookupBySidName(question.getSid(), question.getEntityFrom()) == null) {
				throw new DAOException("entityFrom with correct sid must exist in EntityClass table");
			}
			if (!(question.getEntityTo().equals("")) && ecDAO.lookupBySidName(question.getSid(), question.getEntityTo()) == null) {
				throw new DAOException("entityFrom with correct sid must exist in EntityClass table");
			}
			
			Transaction.begin();
			Question dbQuestion = factory.create(question.getQid());
			factory.copyInto(question, dbQuestion);
			qid++;
			Transaction.commit();
		} catch (DuplicateKeyException e) {
			throw new DAOException("A question with qid " + question.getQid() + " already exists");
		} catch (RollbackException e) {
			throw new DAOException(e);
		} finally {
			if (Transaction.isActive())
				Transaction.rollback();
		}
	}

	public Question lookup(int qid) throws DAOException {
		try {
			return factory.lookup(qid);
		} catch (RollbackException e) {
			throw new DAOException(e);
		}
	}
	
	public Question[] getQuestionBySid(int sid) throws DAOException {
		try {
			return factory.match(MatchArg.equals("sid", sid));
		} catch (RollbackException e) {
			throw new DAOException(e);
		}
	}
	
	public Question[] getQuestionBySidDynetmlType(int sid, int dynetmlType) throws DAOException {
		try {
			return factory.match(MatchArg.equals("sid", sid), MatchArg.equals("dynetmlType", dynetmlType));
		} catch (RollbackException e) {
			throw new DAOException(e);
		}
	}
	
	// Returns a list of questions by sid sorted by qnum. If truncate is true, the question text would be
	// truncated to at most 40 characters. Generally this value would be false.
	public Question[] getQuestionBySidSortedQnum(int sid, boolean truncate) throws DAOException {
		Connection con = null;
		try {
			con = getConnection();
			con.setAutoCommit(true);
			
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM question WHERE sid=" + sid + " ORDER BY qnum ASC");
			List<Question> list = new ArrayList<Question>();
			while (rs.next()) {
				Question question = new Question(rs.getInt("qid"));
				question.setSid(rs.getInt("sid"));
				question.setQnum(rs.getInt("qnum"));
				question.setType(rs.getInt("type"));
				question.setDynetmlAttr(rs.getString("dynetmlAttr"));
				question.setDynetmlType(rs.getInt("dynetmlType"));
				question.setEntityFrom(rs.getString("entityFrom"));
				question.setEntityTo(rs.getString("entityTo"));
				String questionText = rs.getString("question");
				if (truncate) {
					if (questionText.length() <= 40)
						question.setQuestion(questionText);
					else
						question.setQuestion(questionText.substring(0, 37) + "...");
				}
				else
					question.setQuestion(questionText);
				list.add(question);
			}
			rs.close();
			stmt.close();
			releaseConnection(con);
			
			return list.toArray(new Question[list.size()]);
		} catch (SQLException e) {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e2) {}
			throw new DAOException(e);
		}
	}
	
	//Returns all non-network questions in a given survey.
	//Based on invariant that all network questions must have dynetmlType == -1.
	public Question[] getNonNetworkQuestionBySid(int sid) throws DAOException {
		try {
			return factory.match(MatchArg.equals("sid", sid), MatchArg.notEquals("dynetmlType", -1));
		} catch (RollbackException e) {
			throw new DAOException(e);
		}
	}
	
	//Returns all network questions in a given survey.
	//Based on invariant that all network questions must have dynetmlType == -1.
	public Question[] getNetworkQuestionBySid(int sid) throws DAOException {
		try {
			return factory.match(MatchArg.equals("sid", sid), MatchArg.equals("dynetmlType", -1));
		} catch (RollbackException e) {
			throw new DAOException(e);
		}
	}
	
	// Gets a unique question by its sid and qnum. Based on invariant that (sid,qnum) tuple is unique.
	public Question getQuestionBySidQnum(int sid, int qnum) throws DAOException {
		try {
			Question[] question = factory.match(MatchArg.equals("sid", sid), MatchArg.equals("qnum", qnum));
			if (question.length != 1)
				throw new RollbackException("Theres more than one question!");
			return question[0];
		} catch (RollbackException e) {
			throw new DAOException(e);
		}
	}
	
	// Gets all questions who have the same sid but whose qnum is greater than the one specified.
	public Question[] getQuestionBySidQnumGreater(int sid, int qnum) throws DAOException {
		try {
			return factory.match(MatchArg.equals("sid", sid), MatchArg.greaterThan("qnum", qnum));
		} catch (RollbackException e) {
			throw new DAOException(e);
		}
	}
	
	// Update entityFrom, entityTo
	public void updateEntityToFrom(Question question, EntityClassDAO ecDAO) throws DAOException {
		try {
			Transaction.begin();
			Question dbQuestion = lookup(question.getQid());
			if (dbQuestion == null)
				throw new DAOException("Question with qid " + question.getQid() + " doesn't exist");
			if (ecDAO.lookupBySidName(question.getSid(), question.getEntityFrom()) == null)
				throw new DAOException("entityFrom with correct sid must exist in EntityClass table");
			if (ecDAO.lookupBySidName(question.getSid(), question.getEntityTo()) == null)
				throw new DAOException("entityTo with correct sid must exist in EntityClass table");
			
			dbQuestion.setEntityFrom(question.getEntityFrom());
			dbQuestion.setEntityTo(question.getEntityTo());
			Transaction.commit();
		} catch (RollbackException e) {
			throw new DAOException(e);
		} finally {
			if (Transaction.isActive())
				Transaction.rollback();
		}
	}
	
	// Update everything except sid, entityFrom, entityTo
	// Basically, update everything that is not a foreign key
	public void updateQuestion(Question question) throws DAOException {
		try {
			Transaction.begin();
			Question dbQuestion = lookup(question.getQid());
			if (dbQuestion == null)
				throw new DAOException("Question with qid " + question.getQid() + " doesn't exist");
			dbQuestion.setDynetmlAttr(question.getDynetmlAttr());
			dbQuestion.setDynetmlType(question.getDynetmlType());
			dbQuestion.setQuestion(question.getQuestion());
			dbQuestion.setQnum(question.getQnum());
			dbQuestion.setType(question.getType());
			Transaction.commit();
		} catch (RollbackException e) {
			throw new DAOException(e);
		} finally {
			if (Transaction.isActive())
				Transaction.rollback();
		}
	}
	
	public void updateQnum(Question question) throws DAOException {
		try {
			Transaction.begin();
			Question dbQuestion = lookup(question.getQid());
			if (dbQuestion == null)
				throw new DAOException("Question with qid " + question.getQid() + " doesn't exist");
			dbQuestion.setQnum(question.getQnum());
			Transaction.commit();
		} catch (RollbackException e) {
			throw new DAOException(e);
		} finally {
			if (Transaction.isActive())
				Transaction.rollback();
		}
	}
	
	//Returns maximum qnum given an sid.
	public int getMaxQnumBySid(int sid) throws DAOException {
		Connection con = null;
		try {
			con = getConnection();
			con.setAutoCommit(true);
			
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT MAX(qnum) FROM question WHERE sid=" + sid);
			rs.next();
			int answer = rs.getInt(1);
			rs.close();
			stmt.close();
			releaseConnection(con);
			
			return answer;
		} catch (SQLException e) {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e2) {}
			throw new DAOException(e);
		}
	}
	
	public void delete(int qid) throws DAOException {
		try {
			Transaction.begin();
			Question dbQuestion = lookup(qid);
			if (dbQuestion == null)
				throw new DAOException("Question with qid " + qid + " doesn't exist");
			factory.delete(qid);
			Transaction.commit();
		} catch (RollbackException e) {
			throw new DAOException(e);
		} finally {
			if (Transaction.isActive())
				Transaction.rollback();
		}
	}

	public int size() throws DAOException {
		try {
			return factory.getBeanCount();
		} catch (RollbackException e) {
			throw new DAOException(e);
		}
	}
	
	public int getQid() {
		return qid;
	}
}
